// cd /projects/hsieh_project/proj_201809/code_2_201910/
// qstata --dofile=out_city_growth.do --statatype=mp --cpucount=5 &

set linesize 255
capture log close
if "`c(mode)'" == "" log using /projects/hsieh_project/proj_201809/code_2_201910/out_city_growth_log, replace text

clear all
capture noi program drop _all
cd /projects/

di "Started at $S_TIME $S_DATE"

global rev_date: display %tdYYNNDD date("$S_DATE", "DMY")
di "${rev_date}"

global dir_proj "/projects/hsieh_project/proj_201809/"

global dir_do "${dir_proj}/201910_main/"
global dir_data "${dir_proj}/data/"
global dir_out "${dir_proj}/output/201910_main/"
noi capture mkdir ${dir_out}
global dir_outf "${dir_out}/city_growth"
capture mkdir ${dir_outf}

global year1 = 1977
global year2 = 2013
global yearm = 1992

global gl_perc = "10"
local l_perc = "10"

// Load functions
do "/projects/hsieh_project/code_0_general/f_rounding.do"
do "/projects/hsieh_project/code_0_general/f_lpolyci.do"


//==============================================================================
// Changes in City

capture program drop read_city
program read_city
	args i_city
	
	di "`i_city'"
	//local i_city = "msa"
	
	use "${dir_data}/cityind_sum_`i_city'_all", clear
	gen city = `i_city'
	// keep if city_code == "`i_city'"
	
	merge m:1 year `i_city' ch_ind using "${dir_data}/cityind_sum_`i_city'_all_t1"
	gen c_ind_t1_10_ = 1 if _merge == 3
	gen c_ind_ = 1
	
	gen c_emp_mfg_ = emp_cind if sector == 1
	gen c_est_mfg_ = est_cind if sector == 1
	gen c_firm_mfg_ = n_cind if sector == 1
	
	gen c_emp_nmfg_ = emp_cind if sector != 1
	gen c_est_nmfg_ = est_cind if sector != 1
	gen c_firm_nmfg_ = n_cind if sector != 1

	collapse (sum) emp_cind est_cind n_cind c_*_mfg_ c_*_nmfg_ c_ind_t1_10_ c_ind_, by(year city)	
	rename (emp_cind est_cind n_cind) (c_emp_ c_est_ c_firm_)
	
	gen c_inds_t1_10_ = c_ind_t1_10_ / c_ind_
	reshape wide c_emp_ c_est_ c_firm_ ///
		c_emp_mfg c_est_mfg c_firm_mfg ///
		c_emp_nmfg c_est_nmfg c_firm_nmfg ///
		c_ind_t1_10_ c_ind_ c_inds_t1_10_, i(city) j(year)
	
	foreach i_var in emp est emp_mfg est_mfg emp_nmfg est_nmfg inds_t1_10 {
		gen c_`i_var'_d_${year1}_${year2} = c_`i_var'_${year2} - c_`i_var'_${year1}
		gen c_`i_var'_d_${year1}_${yearm} = c_`i_var'_${yearm} - c_`i_var'_${year1}
		gen ln_c_`i_var'_${year1} = ln(c_`i_var'_${year1})
		gen ln_c_`i_var'_d_${year1}_${year2} = ln(c_`i_var'_${year2}) - ln(c_`i_var'_${year1})
		gen ln_c_`i_var'_d_${year1}_${yearm} = ln(c_`i_var'_${yearm}) - ln(c_`i_var'_${year1})
	}

end

capture program drop city_growth
program city_growth
	args i_city v_y t_y t_title t_subtitle
	local t_city = upper("`i_city'")
	
	if 0 {
	twoway (scatter `v_y'_${year1}_${yearm} ln_c_emp_${year1}, m(sh)) ///
		(scatter `v_y'_${year1}_${year2} ln_c_emp_${year1}, m(oh)), ///
		ytitle("`t_y'") xtitle("ln(City Employment in ${year1})") ///
		legend(order(1 "${year1}-${yearm}" 2 "${year1}-${year2}")) ///
		title("`t_title'") subtitle("`t_city'")
	if "`c(mode)'" == "" graph export "${dir_outf}/scat_`v_y'_city_emp_${year1}_`i_city'.png", replace width(3000) height(2000)
	}
	
	
	//local v_y = "ln_c_emp_d"
	//local i_city="fips"
	lpolyci_data "" "lpoly_fig1" "`v_y'_${year1}_${yearm}" "ln_c_emp_${year1}" "99" "100"
	lpolyci_data "" "lpoly_fig2" "`v_y'_${year1}_${year2}" "ln_c_emp_${year1}" "99" "100"
	frame lpoly_fig2 {
		preserve
		keep `v_y'_${year1}_${year2}_x `v_y'_${year1}_${year2}_s `v_y'_${year1}_${year2}_se
		rounding_4dig "`v_y'_${year1}_${year2}_x `v_y'_${year1}_${year2}_s `v_y'_${year1}_${year2}_se"
		export excel ${dir_outf}/lpoly_city_growth.xlsx, sheet("`i_city'_`v_y'") sheetreplace firstrow(var) keepcellfmt
		restore
	}
	frame lpoly_fig1 {
		frlink 1:1 row_id, frame(lpoly_fig2)
		frget _all, from(lpoly_fig2)
		
		local v_y1 = "`v_y'_${year1}_${yearm}"
		local v_y2 = "`v_y'_${year1}_${year2}"
		
		if "`i_city'"=="fips" {
			foreach i_suf in cil ciu s x {
				replace `v_y1'_`i_suf' = . if `v_y1'_x <5
				replace `v_y2'_`i_suf' = . if `v_y2'_x <5
			}
			local c_xlab "xlab(5(2)15)"
		}
		else if inlist("`i_city'","msa","msa0") {
			foreach i_suf in cil ciu s x {
				replace `v_y1'_`i_suf' = . if `v_y1'_x <9
				replace `v_y2'_`i_suf' = . if `v_y2'_x <9
			}
			local c_xlab "xlab(9(2)15)"
		}
		
		if "`i_city'" == "msa" {
			local t_city1 "MSA_CINDNEW_M0F*"
		}
		if "`i_city'" == "fips" {
			local t_city1 "FIPS_CINDNEW_M*F0"
		}
		if "`v_y'" == "ln_c_emp_d" local t_sample = "ALL"
		if "`v_y'" == "ln_c_emp_mfg_d" local t_sample = "ALL, ALL_S1, ALL_S{2-6,9}"
		if "`v_y'" == "ln_c_emp_nmfg_d" local t_sample = "ALL, ALL_S{2-6,9}, ALL_S1"
		if "`v_y'" == "c_inds_t1_10_d" local t_sample = "ALL, CINDT1_IND10_`t_city1', CINDT1*_IND10*_*_CINDNEW_*"
		
		/*
		twoway (rarea `v_y1'_cil `v_y1'_ciu `v_y1'_x, color(none) fi(inten0) lc(gs12) lp(dash) ) ///
			(connected `v_y1'_s `v_y1'_x, m(i) lcolor(navy) lw(medthick) lp(dash_dot)) ///
			(rarea `v_y2'_cil `v_y2'_ciu `v_y2'_x, color(none) fi(inten0) lc(gs12) lp(dash)) ///
			(connected `v_y2'_s `v_y2'_x, m(i) lcolor(maroon) lw(medthick)), ///
			legend(order(2 "${year1}-${yearm}" 4 "${year1}-${year2}")) ///
			ytitle("`t_y'") xtitle("ln(City Employment in ${year1})") ///
			title("`t_title'") subtitle("`t_city'") `c_xlab'
		graph export "${dir_outf}/lpoly_`v_y'_city_emp_${year1}_`i_city'.png", replace width(3000) height(2000)
		*/
		
		twoway (rarea `v_y2'_cil `v_y2'_ciu `v_y2'_x, color(none) fi(inten0) lc(gs8) lw(thin)) ///
			(connected `v_y2'_s `v_y2'_x, m(i) lcolor(navy) lw(medthick)), ///
			legend(off) ///
			ytitle("`t_y'") xtitle("ln(City Employment in ${year1})") ///
			title("`t_title', ${year1}-${year2}") subtitle("`t_city'`t_subtitle', 99% CI") `c_xlab' note("Sample: `t_sample'")
		if "`c(mode)'" == "" graph export "${dir_outf}/lpoly_`v_y'_city_emp_${year1}_`i_city'.png", replace width(3000) height(2500)
		graph save "${dir_outf}/lpoly_`v_y'_city_emp_${year1}_`i_city'", replace
		
		/*
		twoway (connected `v_y1'_s `v_y1'_x, m(i) lcolor(navy) lw(medthick) lp(dash_dot)) ///
			(connected `v_y2'_s `v_y2'_x, m(i) lcolor(maroon) lw(medthick)), ///
			legend(order(1 "${year1}-${yearm}" 2 "${year1}-${year2}")) ///
			ytitle("`t_y'") xtitle("ln(City Employment in ${year1})") ///
			title("`t_title'") subtitle("`t_city'`t_subtitle'") `c_xlab'
		graph export "${dir_outf}/lpoly_`v_y'_city_emp_${year1}_`i_city'_noci.png", replace width(3000) height(2000)
		*/
	}
	
end


capture program drop out_dist
program out_dist
	args i_var v_sect

	local ll_perc = "9 10 11 24 25 26 49 50 51 74 75 76 89 90 91"
	local col_perc = ""
	foreach ii_perc in `ll_perc' {
		local col_perc = "`col_perc' (p`ii_perc') i_var_p`ii_perc'=`i_var'"
	}
	collapse `col_perc' (mean) i_var_mean=`i_var' (sd) i_var_sd=`i_var', by(`v_sect')
	
	rename i_var_mean mean
	rename i_var_sd sd
	egen p10 = rowmean(i_var_p9 i_var_p10 i_var_p11)
	egen p25 = rowmean(i_var_p24 i_var_p25 i_var_p26)
	egen p50 = rowmean(i_var_p49 i_var_p50 i_var_p51)
	egen p75 = rowmean(i_var_p74 i_var_p75 i_var_p76)
	egen p90 = rowmean(i_var_p89 i_var_p90 i_var_p91)
	drop i_var_p*
	gen var = "`i_var'"
end

global ds_decile "${dir_outf}/city_growth_decile"
clear
save "${ds_decile}", replace emptyok

capture program drop f_out_main
program f_out_main
    args vgeo
    
    // local vgeo = "msa"
    di "--------------------------------------------------------------------------------"
    di "Geo: `vgeo'"
    
    di "-----------------------------------------"
    di "Load Data"
	if inlist("`vgeo'","msa1983cz_m") {
		read_city "msa1983cz"
		keep if city < 100000
	}
	else if inlist("`vgeo'","msa1983cz_c") {
		read_city "msa1983cz"
		keep if city > 100000
	}
	else {
		read_city "`vgeo'"
	}
    
    di "-----------------------------------------"
    di "Output growth figures"
    city_growth "`vgeo'" "ln_c_emp_d" "d ln(City Employment)" "City Emp Growth vs City Initial Emp"
    //city_growth "`vgeo'" "ln_c_est_d" "d ln(City Establishments)" "City Est Growth vs City Initial Emp"
    city_growth "`vgeo'" "c_inds_t1_10_d" "d Share of Top 1 City-Ind being Top 10% Ind" "d Share of Top 1 City-Ind being Top 10% Ind"

    city_growth "`vgeo'" "ln_c_emp_mfg_d" "d ln(City Employment)" "City Emp Growth vs City Initial Emp" ", MfG"
    city_growth "`vgeo'" "ln_c_emp_nmfg_d" "d ln(City Employment)" "City Emp Growth vs City Initial Emp" ", Non-MfG"
    
    if inlist("`vgeo'","msa") {
    di "-----------------------------------------"
    di "Output growth table for MSA"
    
    preserve
    keep city c_emp_1977 c_emp_2013 ln_c_emp_d_1977_2013
    gen msa = city
    merge 1:1 msa using /projects/data_commons/cw/cw_geo_msa_str, assert(match using) keep(match)
    list if _merge != 3
    keep if _merge == 3
    drop _merge msa
    gsort -c_emp_1977
    rounding_N "c_emp_1977 c_emp_2013"
    rounding_4dig "ln_c_emp_d_1977_2013"
    export excel ${dir_outf}/tab_city_growth.xlsx, sheet("`vgeo'") sheetreplace firstrow(var) keepcellfmt
    restore
    }
    
    di "-----------------------------------------"
    di "Output table by decile"
    preserve
    keep city c_emp_1977 c_emp_2013 ln_c_emp_d_1977_2013
    list if ln_c_emp_d_1977_2013 == .
    drop if ln_c_emp_d_1977_2013 == .
    egen c_emp_1977_rank = rank(c_emp_1977)
    gen c_emp_1977_perc = c_emp_1977_rank / _N
    gen c_decile = floor(10*c_emp_1977_perc) + 1
    replace c_decile = 10 if c_decile == 11
    //collapse (mean) mean=ln_c_emp_d_1977_2013 (sd) sd=ln_c_emp_d_1977_2013 (p10) p10=ln_c_emp_d_1977_2013 (p90) p90=ln_c_emp_d_1977_2013, by(c_decile)
    out_dist "ln_c_emp_d_1977_2013" "c_decile"
    keep c_decile mean sd p10 p90
    rounding_4dig "mean sd p10 p90"
    gen city = upper("`vgeo'")
    append using ${ds_decile}
    save ${ds_decile}, replace
    export excel ${dir_outf}/tab_city_growth.xlsx, sheet("`vgeo'_decile") sheetreplace firstrow(var) keepcellfmt
    restore
    
    *Save plotting data
    keep ln_c_emp_1977 ln_c_emp_d_1977_2013 c_inds_t1_10_d_1977_2013 
    save "${dir_outf}/plotting_data_`vgeo'", replace
    
end

f_out_main "msa1983"
f_out_main "fips"
/*
f_out_main "msa"
f_out_main "czone"
f_out_main "msa1983cz"
f_out_main "msa1983cz_m"
f_out_main "msa1983cz_c"
*/

/*
use ${ds_decile}, clear
gen sample = ""
tostring c_decile, replace
replace sample = upper(city) + "_DECILE" + c_decile
order city c_decile, first
export excel results/flin/201809/201910_main/Output.xlsx, sheet("city_emp_d") sheetreplace firstrow(var) keepcellfmt
*/

di "Ended at $S_DATE $S_TIME"
capture noi log close
// End of do file
